package Teacher_Course_Student;
import java.sql.*;
import Grade.*;
import Main.UserLogin;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Scanner;
public class TeacherView {
    // 显示操作菜单
    public static void displayMenu() {
        System.out.println("\n=========================================");
        System.out.println("            教师界面");
        System.out.println("=========================================");
        System.out.println("请选择操作：");
        System.out.println("1. 录入成绩");
        System.out.println("2. 修改成绩");
        System.out.println("3. 删除成绩");
        System.out.println("4. 查询成绩");
        System.out.println("5. 退出到主界面");
        System.out.println("=========================================");
    }
    // 控制学生界面的操作流程
    public static void teacher() throws Exception {
        Scanner scanner = new Scanner(System.in);
        boolean running = true;

        while (running) {

            displayMenu();

            // 获取用户输入
            System.out.print("请输入您的选择: ");
            int choice = scanner.nextInt();

            // 清理输入缓冲区中的换行符
            scanner.nextLine();  // 清空输入缓冲区中的换行符
            GradeDAO grade=new Grade();
            int courseId= UserLogin.getCourseId();
            String Tname=UserLogin.getUsername();
            // 根据用户输入执行相应操作
            switch (choice) {
                case 1:
                    System.out.println("已选修你的课程的学生：");
                    Teacher_Student.course();
                    System.out.println("学生成绩：");
                    grade.getAllGrade(Tname);
                    System.out.println("请输入学生学号：");
                    String id1 = scanner.nextLine();
                    System.out.println("请输入该学生的平时成绩：");
                    double aGrade1 = scanner.nextDouble();
                    System.out.println("请输入该学生的考试成绩：");
                    double bGrade1 = scanner.nextDouble();
                    grade.insertGrade(id1,courseId,aGrade1,bGrade1);
                    break;
                case 2:
                    System.out.println("学生成绩：");
                    grade.getAllGrade(Tname);
                    System.out.println("请输入要更新的学生学号：");
                    String id2 = scanner.nextLine();
                    System.out.println("请输入更新后的平时成绩：");
                    double aGrade2 = scanner.nextDouble();
                    System.out.println("请输入更新后的考试成绩：");
                    double bGrade2 = scanner.nextDouble();
                    grade.updateGrade(id2,courseId,aGrade2,bGrade2);
                    break;
                case 3:
                    System.out.println("学生成绩：");
                    grade.getAllGrade(Tname);
                    System.out.println("请输入要删除成绩的学生学号：");
                    String id3 = scanner.nextLine();
                    grade.deleteGrade(id3,courseId);
                    break;
                case 4:
                    grade.getAllGrade(Tname);
                    String url = "jdbc:sqlserver://localhost:1433;databaseName=Student_Grade_Management_System;trustServerCertificate=true";
                    String user = "ysy";
                    String password = "051018";
                    Connection conn = null;
                    PreparedStatement ps1 = null;
                    ResultSet rs1 = null;
                    PreparedStatement ps2 = null;
                    ResultSet rs2 = null;
                    try {
                        // 1. 获取数据库连接
                        conn = DriverManager.getConnection(url, user, password);

                        // 第一个查询：获取指定教师的平均总评成绩
                        String sql1 = "SELECT AVG(总评成绩) FROM 总评成绩 WHERE 教师姓名 = ?";
                        ps1 = conn.prepareStatement(sql1);
                        ps1.setString(1, Tname); // 设置查询条件教师姓名
                        rs1 = ps1.executeQuery();

                        // 获取并输出平均总评成绩
                        if (rs1.next()) {
                            double avgScore = rs1.getDouble(1);
                            System.out.println("你所带的学生的平均总评成绩是：" + avgScore);
                        } else {
                            System.out.println("没有找到符合条件的记录。");
                        }

                        // 第二个查询：查询总评成绩低于 60 的学生
                        String sql2 = "SELECT 学号, 平时成绩, 考试成绩, 总评成绩 FROM 总评成绩 WHERE 总评成绩 < 60";
                        ps2 = conn.prepareStatement(sql2);
                        rs2 = ps2.executeQuery();

                        // 输出总评成绩低于 60 的学生信息
                        System.out.println("\n以下是总评成绩低于 60 的学生：");
                        boolean hasLowScores = false;  // 检查是否有低于 60 的成绩
                        while (rs2.next()) {
                            hasLowScores = true;
                            String studentId = rs2.getString("学号");
                            double dailyScore = rs2.getDouble("平时成绩");
                            double examScore = rs2.getDouble("考试成绩");
                            double totalScore = rs2.getDouble("总评成绩");
                            System.out.println("学号: " + studentId + ", 平时成绩: " + dailyScore + ", 考试成绩: " + examScore + ", 总评成绩: " + totalScore);
                        }

                        // 如果没有低于 60 的学生成绩
                        if (!hasLowScores) {
                            System.out.println("没有总评成绩低于 60 的学生。");
                        }

                    } catch (SQLException e) {
                        e.printStackTrace();  // 输出数据库操作相关错误
                    } finally {
                        // 关闭资源，防止内存泄漏
                        try {
                            if (rs1 != null) rs1.close();
                            if (ps1 != null) ps1.close();
                            if (rs2 != null) rs2.close();
                            if (ps2 != null) ps2.close();
                            if (conn != null) conn.close();
                        } catch (SQLException e) {
                            e.printStackTrace();  // 输出关闭资源相关的错误
                        }
                    }

                    break;
                case 5:
                    // 选择退出到主界面
                    System.out.println("\n=========================================");
                    System.out.println("正在返回主界面...");
                    System.out.println("=========================================");
                    running = false;  // 退出学生界面，返回到主界面
                    break;
                default:
                    System.out.println("\n输入无效，请重新选择！");
                    break;
            }
        }

    }
}
